﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;
using T4UtilityBelt.DataDesigns;

namespace T4UtilityBelt.SqlCe
{
    public class SqlCeUpdater
    {
        public IEnumerable<string> BuildCreateTableQueries(T4UtilityBelt.DataDesigns.EntityModel entityModel)
        {
            foreach (Entity entity in entityModel.Entities)
            {
                yield return BuildCreateTableQuery(entity);
            }
        }

        //private string BuildDropTableQuery(Entity entity)
        //{
        //    // "create table CoolPeople (LastName nvarchar (40) not null, FirstName nvarchar (40), URL nvarchar (256) )"
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("drop table ");
        //    sb.Append(entity.Name);

        //    return sb.ToString();
        //}

        private static string BuildCreateTableQuery(Entity entity)
        {
            // "create table CoolPeople (id int PRIMARY KEY CLUSTERED, LastName nvarchar (40) not null, FirstName nvarchar (40), URL nvarchar (256) )"
            StringBuilder sb = new StringBuilder();
            sb.Append("create table ");
            sb.Append(entity.Name);
            sb.Append("(");
            bool first = true;
            foreach (Property property in entity.Properties)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    sb.Append(", ");
                }
                sb.Append(property.Name);
                sb.Append(" ");
                sb.Append(property.GetSqlType());
                if (property is KeyProperty)
                {
                    sb.Append(" PRIMARY KEY");
                }
                else
                {


                    if (property.Required)
                    {
                        sb.Append(" not null");
                    }
                    else
                    {
                        sb.Append(" null");
                    }
                }
            }

            sb.Append(")");
            return sb.ToString();
        }

        public static string CreateSqlCeConnection(string fileName)
        {
            SqlCeConnectionStringBuilder sb = new SqlCeConnectionStringBuilder();
            sb.DataSource = fileName;

            return sb.ToString();
        }

        public static void CreateDatabase(string fileName)
        {
            if (File.Exists(fileName))
                File.Delete(fileName);

            using (SqlCeEngine engine = new SqlCeEngine(CreateSqlCeConnection(fileName)))
            {
                engine.CreateDatabase();
            }
        }

        public static void CreateTables(string fileName, T4UtilityBelt.DataDesigns.EntityModel entityModel)
        {
 
            SqlCeConnection cn = null;
            try
            {
                cn = new SqlCeConnection(CreateSqlCeConnection(fileName));
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                SqlCeCommand cmd;
                foreach (Entity entity in entityModel.Entities)
                {
                    //try
                    //{
                    //    cmd = new SqlCeCommand(BuildDropTableQuery(entity), cn);
                    //    cmd.ExecuteNonQuery();
                    //}
                    //catch 
                    //{}

                    //try
                    //{
                    using (cmd = new SqlCeCommand(BuildCreateTableQuery(entity), cn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    //}
                    //catch
                    //{ }
                }

            }
            //catch (SqlCeException sqlexception)
            //{
            //    //todo
            //}
            //catch (Exception ex)
            //{
            //    // todo
            //}
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                }
            }
        }

        public static void CreateTables(string fileName, IEnumerable<string> tables)
        {
            SqlCeConnection cn = null;
            try
            {
                cn = new SqlCeConnection(CreateSqlCeConnection(fileName));
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                foreach (string table in tables)
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(table, cn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
                //catch (SqlCeException sqlexception)
                //{
                //    //todo
                //}
                //catch (Exception ex)
                //{
                //    // todo
                //}
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                }
            }
        }
    }
}